import pandas as pd
import math
import time
import logging
from typing import Optional
import conf  # 假设这是您的配置模块

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)


def process_excel_mes_routing(db, excel_file_path: str) -> None:
    """
    从Excel文件读取数据并写入Routing相关数据库表

    参数:
        db: 自定义的数据库连接对象
        excel_file_path: Excel文件路径
    """
    try:
        # 读取Excel文件
        logger.info(f"开始读取Excel文件: {excel_file_path}")
        df = pd.read_excel(excel_file_path)
        logger.info(f"成功读取Excel文件，共{len(df)}行数据")
        current_item_id = None  # 当前行处理的item_id
        current_param_id = None  # 当前行处理的param_id

        # 遍历每一行数据
        for index, row in df.iterrows():
            row_num = index + 2  # 计算Excel实际行号（包含标题行）
            print("row number: " + str(row_num))
            try:
                # 处理mes_routing_base_item表数据（当工序名称不为空时）
                print("row['工序名称']: " + str(row['工序名称']))
                if not pd.isna(row['工序名称']):
                    current_item_id = insert_mes_routing_base_item(
                        db,
                        row['工序名称'],
                        row['工序说明'] if not pd.isna(row['工序说明']) else None,
                        row['预期工序流转时间'] if not pd.isna(row['预期工序流转时间']) else None,
                        row['加工工时'] if not pd.isna(row['加工工时']) else None,
                        row['准备工时'] if not pd.isna(row['准备工时']) else None,
                        row['总工时'] if not pd.isna(row['总工时']) else None,
                        row['mes管控'] if not pd.isna(row['mes管控']) else 1,
                        row['生产领料'] if not pd.isna(row['生产领料']) else 0,
                        row['工位id'] if not pd.isna(row['工位id']) else None
                    )

                    if not current_item_id:
                        logger.warning(f"第{row_num}行: 插入mes_routing_base_item失败，跳过此行处理")
                        continue

                    logger.debug(f"第{row_num}行: 已插入mes_routing_base_item，ID: {current_item_id}")

                print("row['参数代号']" + str(row['参数代号']))
                # 处理mes_routing_base_param表数据（当参数代号不为空且已获取item_id时）
                if not pd.isna(row['参数代号']) and current_item_id:
                    # 处理数字类型的空值
                    param_sort = 0 if pd.isna(row.get('参数序号')) else row['参数序号']
                    param_default_val = "" if pd.isna(row.get('参数默认值')) else row['参数默认值']
                    param_max_val = "" if pd.isna(row.get('参数最大值')) else row['参数最大值']
                    param_min_val = "" if pd.isna(row.get('参数最小值')) else row['参数最小值']
                    input_type = "" if pd.isna(row.get('输入类型')) else row['输入类型']

                    current_param_id = insert_mes_routing_base_param(
                        db,
                        current_item_id,
                        row['参数代号'],
                        row['参数名称'] if not pd.isna(row['参数名称']) else None,
                        row['参数类型'] if not pd.isna(row['参数类型']) else None,
                        param_sort,
                        param_default_val,
                        param_max_val,
                        param_min_val,
                        row['必填标记'] if not pd.isna(row['必填标记']) else 0,
                        row['管控标记'] if not pd.isna(row['管控标记']) else 1,
                        input_type
                    )

                    if current_param_id:
                        logger.debug(f"第{row_num}行: 已插入mes_routing_base_param，ID: {current_param_id}")
                    else:
                        logger.warning(f"第{row_num}行: 插入mes_routing_base_param失败")

                print("row['控制方法']" + str(row['控制方法']))
                # 处理mes_routing_base_check表数据（当控制方法不为空且已获取item_id时）
                if not pd.isna(row['控制方法']) and current_item_id:
                    print("################check################")
                    print(current_item_id,
                        current_param_id if current_param_id else None,
                        row['控制方法'],
                        row['检验类型'] if not pd.isna(row['检验类型']) else None,
                        row['检验数量'] if not pd.isna(row['检验数量']) else None,
                        row['检验单位'] if not pd.isna(row['检验单位']) else "",
                        row['检验频次'] if not pd.isna(row['检验频次']) else None,
                        row['检验频次单位'] if not pd.isna(row['检验频次单位']) else "",
                        row['启用标识'] if not pd.isna(row['启用标识']) else 1,
                        row['备注'] if not pd.isna(row['备注']) else "")
                    print("################check################")
                    check_id = insert_mes_routing_base_check(
                        db,
                        current_item_id,
                        current_param_id if current_param_id else None,
                        row['控制方法'],
                        row['检验类型'] if not pd.isna(row['检验类型']) else None,
                        row['检验数量'] if not pd.isna(row['检验数量']) else None,
                        row['检验单位'] if not pd.isna(row['检验单位']) else "",
                        row['检验频次'] if not pd.isna(row['检验频次']) else None,
                        row['检验频次单位'] if not pd.isna(row['检验频次单位']) else "",
                        row['启用标识'] if not pd.isna(row['启用标识']) else 1,
                        row['备注'] if not pd.isna(row['备注']) else ""
                    )

                    if check_id:
                        logger.debug(f"第{row_num}行: 已插入mes_routing_base_check，ID: {check_id}")
                    else:
                        logger.warning(f"第{row_num}行: 插入mes_routing_base_check失败")

                logger.info(f"第{row_num}行数据处理完成")

            except Exception as e:
                logger.error(f"处理第{row_num}行数据时出错: {str(e)}", exc_info=True)
                db.end('rollback')  # 回滚当前行操作
                continue

        # 提交所有更改
        db.commit()
        logger.info("所有数据处理完成，已提交到数据库")

    except Exception as e:
        logger.error(f"处理Excel文件时发生错误: {str(e)}", exc_info=True)
        db.end('rollback')  # 发生错误时回滚


def insert_mes_routing_base_item(db, item_name, item_desc, plan_time, work_time,
                                 prepare_time, total_time, mes_manage, obtain, line_station_id) -> Optional[int]:
    """
    插入数据到mes_routing_base_item表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        # 获取操作人信息和时间戳
        op_id = conf.storage_dept_staff["id"]
        op_name = conf.storage_dept_staff["name"]
        op_time = int(time.time() * 1000)

        sql = """
        INSERT INTO mes_routing_base_item 
        (item_name, item_desc, plan_time, work_time, prepare_time, 
         total_time, mes_manage, obtain, line_station_id, creator_id, 
         creator_name, create_time, last_updater_id, last_updater_name, last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            item_name, item_desc, plan_time, work_time, prepare_time,
            total_time, mes_manage, obtain, line_station_id, op_id,
            op_name, op_time, op_id, op_name, op_time
        )

        # 使用自定义数据库类的方法获取自增ID
        item_id = db.insert_one_return_id(sql, params)
        logger.debug(f"插入mes_routing_base_item成功，ID: {item_id}")
        return item_id

    except Exception as e:
        logger.error(f"插入mes_routing_base_item失败: {str(e)}", exc_info=True)
        return None


def insert_mes_routing_base_param(db, item_id, param_no, param_name, param_type, param_sort,
                                  param_default_val, param_max_val, param_min_val, need_write,
                                  need_manage, input_type) -> Optional[int]:
    """
    插入数据到mes_routing_base_param表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        # 获取操作人信息和时间戳
        op_id = conf.storage_dept_staff["id"]
        op_name = conf.storage_dept_staff["name"]
        op_time = int(time.time() * 1000)

        sql = """
        INSERT INTO mes_routing_base_param 
        (item_id, param_no, param_name, param_type, param_sort, 
         param_default_val, param_max_val, param_min_val, need_write, 
         need_manage, input_type, creator_id, creator_name, create_time, 
         last_updater_id, last_updater_name, last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            item_id, param_no, param_name, param_type, param_sort,
            param_default_val, param_max_val, param_min_val, need_write,
            need_manage, input_type, op_id, op_name, op_time,
            op_id, op_name, op_time
        )

        # 使用自定义数据库类的方法获取自增ID
        param_id = db.insert_one_return_id(sql, params)
        logger.debug(f"插入mes_routing_base_param成功，ID: {param_id}")
        return param_id

    except Exception as e:
        logger.error(f"插入mes_routing_base_param失败: {str(e)}", exc_info=True)
        return None


def insert_mes_routing_base_check(db, item_id, param_id, control_method, check_type, check_num, check_unit,
                                  check_rate, check_rate_unit, enabled, remark) -> Optional[int]:
    """
    插入数据到mes_routing_base_check表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        # 获取操作人信息和时间戳
        op_id = conf.storage_dept_staff["id"]
        op_name = conf.storage_dept_staff["name"]
        op_time = int(time.time() * 1000)

        sql = """
        INSERT INTO mes_routing_base_check 
        (item_id, param_id, control_method, check_type, check_num, check_unit, 
         check_rate, check_rate_unit, enabled, remark, creator_id, 
         creator_name, create_time, last_updater_id, last_updater_name, last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            item_id, param_id, control_method, check_type, check_num, check_unit,
            check_rate, check_rate_unit, enabled, remark, op_id,
            op_name, op_time, op_id, op_name, op_time
        )

        # 使用自定义数据库类的方法获取自增ID
        check_id = db.insert_one_return_id(sql, params)
        logger.debug(f"插入mes_routing_base_check成功，ID: {check_id}")
        return check_id

    except Exception as e:
        logger.error(f"插入mes_routing_base_check失败: {str(e)}", exc_info=True)
        return None
